let express=require("express")();
let expressStatic=require("express-static");
let mysql=require("mysql");
let bodyParser = require('body-parser');

express.use(bodyParser.json());
express.use(bodyParser.urlencoded({extended: false}));
const port=8080;

let sql = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : '123456',
    database : 'student',
    timezone:"08:00"
  });

  sql.connect();

express.get("/getstudentlist",(request,response)=>{
    sql.query(`SELECT * FROM students`,(error,data)=>{
        if(error){
            console.log(error)
            response.send("error")
        }
        else{
            response.send(data)
        }
    })
})


express.get("/deletestudent",(request,response)=>{
    // request.query前端给的数据
    sql.query(`DELETE FROM students WHERE id=${request.query.id}`,(error,data)=>{
        if(error){
            console.log(error)
            response.send("error")
        }
        else{
            response.send("success")
        }
    })
})

express.get("/addStudent",(request,response)=>{
	let p = request.query;
	sql.query(`INSERT INTO students (name,age,sex,city,joindate) VALUES ("${p.name}","${p.age}","${p.sex}","${p.city}","${p.joindate}")`,(error,data)=>{
		if(error){
			console.log(error)
			response.send("error")
		}
		else{
			response.send("success")
			console.log(`新增学生${p.name}操作成功`)
		}
	})
})

// 登录请求
express.post("/login",(request,response)=>{
    // 用sql语句查询从前端获取的用户名和密码是否在数据库中
    sql.query(`SELECT * FROM users WHERE username="${request.body.username}" AND password="${request.body.password}"`,(error,data)=>{
        if(error){
            response.send("error");
            console.log(error)
        }
        else{
            if(data.length === 0){
                response.send("error")
            }
            else{
                console.log(data)
                response.send(data)
            }
        }
    })
})
// 注册请求
express.post("/adduser",(request,response)=>{
    console.log(request.body)
    sql.query(`INSERT INTO users (username,password,name,type) VALUES ("${request.body.username}","${request.body.password}","${request.body.name}","${request.body.type}")`,(error,data)=>{
        if(error){
            response.send("error");
            console.log(error);
        }
        else{
            response.send("success");
            console.log(`${request.query.name}用户新增成功！`)
        }
    })
})

// 修改学生信息
express.get("/changemessage",(request,response)=>{
	
	sql.query(` UPDATE students SET name="${request.query.name}",age="${request.query.age}",sex="${request.query.sex}",city="${request.query.city}",joindate="${request.query.joindate}" WHERE id="${request.query.id}"`,(error,data)=>{
		if(error){
			console.log(error)
			response.send("error")
		}
		else{
			response.send("success")
			console.log(`修改学生${request.query.name}操作成功`)
		}
	})
})

express.use(expressStatic(__dirname+'/static'));

console.log(`running at ${port}`)
// 监听
express.listen(port)